package com.mysql.jdbc.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import lombok.extern.slf4j.Slf4j;


import zhongqi.jdbc.dao.ConnectionDao;

import com.mysql.jdbc.ResultSetInternalMethods;
import com.mysql.jdbc.cacheutils.JdbcLruCache;

import com.mysql.jdbc.cacheutils.SqlParse;

/**
 * @author  JYM
 * 2016-10-10 下午4:52:05
 */
@Slf4j
public class ConnectionUtils {
	
	
	/**
	 * @param sql
	 * @param url
	 * @param user
	 * @param pwd
	 * @return
	 */
	
	public static ResultSet getSelectResultSet(String url, String user, String pwd, String sql) {
		//在这里解析sql语句，然后查询sql对应的表有没有缓存
		//有缓存，用sql解析出来的信息在缓存中查询；没缓存，在数据库中查询
		
		ResultSet rs=null;
		Connection con=null;
		Statement stmt=null;
		
		SqlParse sqlparse = new SqlParse();
		sqlparse.parse(sql);
		
		if (JdbcLruCache.checkNotNullOrContainCatalog(sqlparse.getTable())) {
			log.info("从缓存中获取数据！");
			rs=JdbcLruCache.getResultSet(sqlparse);
		}else {
			try {
				 log.info("开始连接数据库！");
				con = DriverManager.getConnection(url,user,pwd);
				log.info("数据库连接成功！");
			     log.info("执行SQL语句！");
				stmt=con.createStatement();
				 rs=stmt.executeQuery(sql);
				  log.info("获取到结果集，即关闭相关对象！");
				 ((ResultSetInternalMethods) rs).setClosed(true); 
				 stmt.close();
				 con.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return rs;
	}

	//插入数据
	public static void insert(String url, String user, String pwd, String sql) {
		
		SqlParse sqlparse = new SqlParse();
		sqlparse.parse(sql);
		
		if (JdbcLruCache.checkNotNullOrContainCatalog(sqlparse.getTable())) {
			log.info("向缓存插入数据！");
			JdbcLruCache.insert(sqlparse);
			log.info("保存sql语句！");
			JdbcLruCache.putTbSql(sqlparse.getTable(), sqlparse.getOriginSql());
		}else {
			try{
				log.info("开始连接数据库！");
				Connection con=DriverManager.getConnection(url, user, pwd);
			    Statement statement=con.createStatement();
			    log.info("数据库连接成功！");
			    log.info("执行SQL语句！");
			    statement.executeUpdate(sql);
			    log.info("插入操作执行完毕！");
			    statement.close();
				con.close();	
			}catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	
	//删除数据
	public static void delete(String url, String user, String pwd, String sql) {
		
		SqlParse sqlparse = new SqlParse();
		sqlparse.parse(sql);
		
		if (JdbcLruCache.checkNotNullOrContainCatalog(sqlparse.getTable())) {
			log.info("缓存数据删除！");
			JdbcLruCache.delete(sqlparse);
			log.info("保存sql语句！");
			JdbcLruCache.putTbSql(sqlparse.getTable(), sqlparse.getOriginSql());
		}else {
			try{
				log.info("开始连接数据库！");
				Connection con=DriverManager.getConnection(url, user, pwd);
			    Statement statement=con.createStatement();
			    log.info("数据库连接成功！");
			    log.info("执行SQL语句！");
			    statement.executeUpdate(sql);
			    log.info("删除操作执行完毕！");
			    statement.close();
				con.close();
			}catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	
	//更新数据
	public static void update(String url, String user, String pwd, String sql) {
		
		SqlParse sqlparse = new SqlParse();
		sqlparse.parse(sql);
		
		if (JdbcLruCache.checkNotNullOrContainCatalog(sqlparse.getTable())) {
			log.info("缓存数据更新！");
			JdbcLruCache.update(sqlparse);
			log.info("保存sql语句！");
			JdbcLruCache.putTbSql(sqlparse.getTable(), sqlparse.getOriginSql());
		}else {
			try{
				log.info("开始连接数据库！");
				Connection con=DriverManager.getConnection(url, user, pwd);
			    Statement statement=con.createStatement();
			    log.info("数据库连接成功！");
			    log.info("执行SQL语句！");
			    statement.executeUpdate(sql);
			    log.info("更新操作执行完毕！");
			    statement.close();
				con.close();	
			}catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	

	/**
	 * 释放resultSet，同时保存表的连接信息，用于后期的回写数据库
	 * @param rs
	 */
	public static void freeRs(ResultSet rs, String url, String user, String password) {
			try {
				if(rs!=null){
					 ((ResultSetInternalMethods) rs).addCacheRow(url, user, password);
					  ((ResultSetInternalMethods) rs).setClosed(false);
					  rs.close();	
				}
				
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		


}
